﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SalesModel;
using System.Data.SqlClient;
using pe.edu.pucp.lp2.controller;

namespace SalesController
{
    class SQLServerDAOSaleDetail: DAOSaleDetail
    {
        public void add(SalesDetail sd, int idSale)
        {
            SqlConnection conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO BoolSaleLineItem(id_product,id_sale,cantidad,subtotal)" +
                " VALUES (@param1,@param2,@param3,@param4)";
                SqlParameter param1 = new SqlParameter("@param1", System.Data.SqlDbType.Int);
                SqlParameter param2 = new SqlParameter("@param2", System.Data.SqlDbType.Int);
                SqlParameter param3 = new SqlParameter("@param3", System.Data.SqlDbType.Int);
                SqlParameter param4 = new SqlParameter("@param4", System.Data.SqlDbType.Float);

                param1.Value = sd.getProduct().getId_product();
                param2.Value = idSale;
                param3.Value = sd.getQuantity();
                param4.Value = sd.getSubTotal();

                comm.Parameters.Add(param1);
                comm.Parameters.Add(param2);
                comm.Parameters.Add(param3);
                comm.Parameters.Add(param4);

                //Paso 4. Ejecutar la sentencia
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
        }
        public void update(SalesDetail sd, int idSale)
        {
            SqlConnection conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE BoolSaleLineItem SET id_product=@param1,id_sale=@param2,cantidad=@param3,subtotal=@param4 WHERE id=@param5";
                SqlParameter param1 = new SqlParameter("@param1", System.Data.SqlDbType.Int);
                SqlParameter param2 = new SqlParameter("@param2", System.Data.SqlDbType.Int);
                SqlParameter param3 = new SqlParameter("@param3", System.Data.SqlDbType.Int);
                SqlParameter param4 = new SqlParameter("@param4", System.Data.SqlDbType.Float);
                SqlParameter param5 = new SqlParameter("@param5", System.Data.SqlDbType.Int);

                param1.Value = sd.getProduct().getId_product();
                param2.Value = idSale;
                param3.Value = sd.getQuantity();
                param4.Value = sd.getSubTotal();
                param5.Value = sd.getIdDetail();

                comm.Parameters.Add(param1);
                comm.Parameters.Add(param2);
                comm.Parameters.Add(param3);
                comm.Parameters.Add(param4);
                comm.Parameters.Add(param5);

                //Paso 4. Ejecutar la sentencia
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
        }
        public void delete(SalesDetail sd)
        {
            SqlConnection conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM BoolSaleLineItem WHERE id=@param1";
                SqlParameter param1 = new SqlParameter("@param1", System.Data.SqlDbType.Int);

                param1.Value = sd.getIdDetail();

                comm.Parameters.Add(param1);

                //Paso 4. Ejecutar la sentencia
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
        }
        public List<SalesDetail> queryById(int saleId)
        {
            List<SalesDetail> list = new List<SalesDetail>();
            SqlConnection conn = null;
            SqlDataReader myReader = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM BoolSaleLineItem WHERE id_sale=@param1";

                SqlParameter param1 = new SqlParameter("@param1", System.Data.SqlDbType.Int);

                param1.Value = saleId;

                comm.Parameters.Add(param1);

                //Paso 4. Ejecutar la sentencia
                myReader = comm.ExecuteReader();

                //Paso 5: Evaluar los resultados
                while (myReader.Read())
                {
                    SalesDetail sd = new SalesDetail();

                    sd.setIdDetail(Int32.Parse(myReader["id"].ToString()));
                    sd.setProduct(SalesManager.getProductById(Int32.Parse(myReader["id_product"].ToString())));
                    sd.setQuantity(Int32.Parse(myReader["cantidad"].ToString()));
                    sd.setSubTotal(Double.Parse(myReader["subtotal"].ToString()));
                   
                    list.Add(sd);
                }
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 6. CERRAR LA CONEXIÓN!!!!
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            return list;
        }
    }
}
